/*
 * The National Parks Database Project
 * Group: Tyler Simrell, Vladmir Dudnic, Michael Jagielo
 * DBInterface.java
 */
package database;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * This class does much of the heavy lifting with SQL.
 * @author Vladmir Dudnic and Tyler Simrell
 * @version 5/29/13
 */
public class DBInterface {
     
     private PreparedStatement preparedStatement = null;
     private ResultSet rs = null;
     private String url = "jdbc:mysql://localhost:3306/NationalParksDB?user=user&password=userpw";
	
	  private Connection conn;
	 
	  /**
	   * This constructor takes no parameters and gets the necessary things working. 
	   */
	  public DBInterface()
	  {
		    try
		    {
			      Class.forName("com.mysql.jdbc.Driver").newInstance();
			      conn = DriverManager.getConnection(url);
			      conn.close();
		    }
		    catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
		    catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
		    catch (InstantiationException ex) {System.err.println(ex.getMessage());}
		    catch (SQLException ex)           {System.err.println(ex.getMessage());}
	  }
	  
	  /**
	   * This method returns a List with each tuple in the Inventory table.
	   * @return A list of tuples in the trail_inventory table.
	   */
	  public List<InventoryItem> getInventoryList()
	  {
          List<InventoryItem> inventoryitems = new ArrayList<InventoryItem>();
          try
          {
                  conn = DriverManager.getConnection(url);
                  preparedStatement = conn.prepareStatement("SELECT * FROM trail_inventory");
                  rs = preparedStatement.executeQuery();
                  while(rs.next()){
                	  InventoryItem item = new InventoryItem(rs.getString("trail_name"), rs.getString("park_name"),
                			  				rs.getString("vista"), rs.getInt("slope"), rs.getInt("length"), 
                			  				rs.getString("terrain"), rs.getString("difficulty"));
                	  inventoryitems.add(item);
                  }
          }
          catch (SQLException ex)
          {
                 Logger lgr = Logger.getLogger(DBInterface.class.getName());
                 lgr.log(Level.SEVERE, ex.getMessage(), ex);
          }
          finally
          {
                  try
                  {
			          if (preparedStatement != null)
			          {
			              preparedStatement.close();
			          }
			          if (conn != null)
			          {
			              conn.close();
			          }
                  }
                  catch (SQLException ex)
                  {
			          Logger lgr = Logger.getLogger(DBInterface.class.getName());
			          lgr.log(Level.SEVERE, ex.getMessage(), ex);
                  }
          }
          try 
          {
			conn.close();
          } 
          catch (SQLException e) 
          {
			e.printStackTrace();
          }
          return inventoryitems;
	  }
	  
	  /**
	   * This method returns all of the tuples in the maint_user table
	   * @return A list of tuples in the maint_user table.
	   */
	  public List<MaintenanceItem> getMaintanceList()
	  {
          List<MaintenanceItem> maintanceitems = new ArrayList<MaintenanceItem>();
          try
          {
                  conn = DriverManager.getConnection(url);
                  preparedStatement = conn.prepareStatement("SELECT * FROM maint_user");
                  rs = preparedStatement.executeQuery();
                  while(rs.next()){
                	  MaintenanceItem item = new MaintenanceItem(rs.getString("trail_name"), 
                			  				 rs.getInt("melt_out"), rs.getInt("wash_out"), rs.getString("open"));
                	  maintanceitems.add(item);
                  }
          }
          catch (SQLException ex)
          {
                 Logger lgr = Logger.getLogger(DBInterface.class.getName());
                 lgr.log(Level.SEVERE, ex.getMessage(), ex);
          }
          finally
          {
                  try
                  {
			          if (preparedStatement != null)
			          {
			              preparedStatement.close();
			          }
			          if (conn != null)
			          {
			              conn.close();
			          }
                  }
                  catch (SQLException ex)
                  {
			          Logger lgr = Logger.getLogger(DBInterface.class.getName());
			          lgr.log(Level.SEVERE, ex.getMessage(), ex);
                  }
          }
          
          try 
          {
			conn.close();
          } 
          catch (SQLException e) 
          {
			e.printStackTrace();
          }
          
          return maintanceitems;
	  }
	  
	  
	  /**
	   * This method adds a tuple to the trail_inventory method.
	   * @param trail_name Name of the trail
	   * @param park_name Name of the National Park
	   * @param vista_point A major vista on the trail
	   * @param slope The slope of the trail (difference of height from the starting point to the ending point
	   * @param length The length of the trail.
	   * @param terrain The terrain of the trail.
	   * @param difficulty The difficulty of the trail.
	   */
	  public void addToInventoryTable(String trail_name, String park_name,
										String vista_point, double slope, double length, 
										String terrain, String difficulty)
      {
              try
              {
                      conn = DriverManager.getConnection(url);
                      preparedStatement = conn.prepareStatement("INSERT INTO trail_inventory VALUES(?, ?, ?, ?, ?, ?, ?)");
                      preparedStatement.setString(1, trail_name);
                      preparedStatement.setString(2, park_name);
                      preparedStatement.setDouble(3, slope);
                      preparedStatement.setString(4, terrain);
                      preparedStatement.setString(5, vista_point);
                      preparedStatement.setDouble(6, length);
                      preparedStatement.setString(7, difficulty);
                      preparedStatement.executeUpdate();
              }
              catch (SQLException ex)
              {
            	  Logger lgr = Logger.getLogger(DBInterface.class.getName());
            	  lgr.log(Level.SEVERE, ex.getMessage(), ex);
              }
              finally
              {
                      try
                      {
			              if (preparedStatement != null)
			              {
			                  preparedStatement.close();
			              }
			              if (conn != null)
			              {
			                  conn.close();
			              }
                      }
                      catch (SQLException ex)
                      {
			              Logger lgr = Logger.getLogger(DBInterface.class.getName());
			              lgr.log(Level.SEVERE, ex.getMessage(), ex);
                      }
              }
              
              try 
              {
    			conn.close();
              } 
              catch (SQLException e) 
              {
    			e.printStackTrace();
              }
      }
	  
	  /**
	   * This method adds to the maintenance table
	   * @param trail_name Name of the Trail
	   * @param melt_out The % of melt out
	   * @param wash_out The % of wash out
	   * @param open Whether or not the trail is open
	   */
	  public void addToMaintenanceTable(String trail_name, double melt_out, double wash_out, 
										String open)
	  {
		  try
		  {
				conn = DriverManager.getConnection(url);
				preparedStatement = conn.prepareStatement("INSERT INTO maint_user VALUES(?, ?, ?, ?)");
				preparedStatement.setString(1, trail_name);
				preparedStatement.setDouble(2, melt_out);
				preparedStatement.setDouble(3, wash_out);
				preparedStatement.setString(4, open);
				preparedStatement.executeUpdate();
		  }
		  catch (SQLException ex)
		  {
				Logger lgr = Logger.getLogger(DBInterface.class.getName());
				lgr.log(Level.SEVERE, ex.getMessage(), ex);
		  }
		  finally
		  {
			  try
			  {
					if (preparedStatement != null)
					{
					    preparedStatement.close();
					}
					if (conn != null)
					{
					    conn.close();
					}
			  }
			  catch (SQLException ex)
			  {
					Logger lgr = Logger.getLogger(DBInterface.class.getName());
					lgr.log(Level.SEVERE, ex.getMessage(), ex);
			  }
		  }
		  try 
          {
			conn.close();
          } 
          catch (SQLException e) 
          {
			e.printStackTrace();
          }
	  }
	  
	  /**
	   * This method updates the Inventory table
	   * @param trail_name Name of the trail
	   * @param park_name Name of the National Park
	   * @param vista_point A major vista on the trail
	   * @param slope The slope of the trail (difference of height from the starting point to the 
	   *              ending point
	   * @param length The length of the trail.
	   * @param terrain The terrain of the trail.
	   * @param difficulty The difficulty of the trail.
	   */
	  public  void updateInventoryTable (String trail_name, String park_name,
												String vista_point, double slope, double length, 
												String terrain, String difficulty)
		{
		    try
		    {
		    	conn = DriverManager.getConnection(url);
		    	// create our java preparedstatement using a sql update query
		    	preparedStatement  = conn.prepareStatement(
				"UPDATE trail_inventory SET park_name = ?, vista = ?, slope = ?, length = ?, " +
				"terrain = ?, difficulty = ? WHERE trail_name = ?");
				
				// set the preparedstatement parameters
                preparedStatement.setString(1, park_name);
                preparedStatement.setString(2, vista_point);
                preparedStatement.setDouble(3, slope);
                preparedStatement.setDouble(4, length);
                preparedStatement.setString(5, terrain);
                preparedStatement.setString(6, difficulty);
                preparedStatement.setString(7, trail_name);
                preparedStatement.executeUpdate();
				// call executeUpdate to execute our sql update statement
                preparedStatement.executeUpdate();
                preparedStatement.close();
		    }
		    catch (SQLException se)
		    {
		    	// log the exception
		    	try 
		          {
					conn.close();
		          } 
		          catch (SQLException e) 
		          {
					e.printStackTrace();
		          }
		    	se.printStackTrace();
		    }
		}
	  
	  /**
	   * This method updates the maint_user table
	   * @param trail_name Name of the Trail
	   * @param melt_out The % of melt out
	   * @param wash_out The % of wash out
	   * @param open Whether or not the trail is open
	   */
	  public  void updateMaintenanceTable (String trail_name, double melt_out, double wash_out, 
										String open)
	  {
			try
			{
				conn = DriverManager.getConnection(url);
				// create our java preparedstatement using a sql update query
				preparedStatement  = conn.prepareStatement(
				"UPDATE maint_user SET melt_out = ?, wash_out = ?, open = ? WHERE trail_name = ?");
				
				// set the preparedstatement parameters
				preparedStatement.setDouble(1, melt_out);
				preparedStatement.setDouble(2, wash_out);
				preparedStatement.setString(3, open);
				preparedStatement.setString(4, trail_name);
			
				System.out.println(preparedStatement.toString());
				// call executeUpdate to execute our sql update statement
				preparedStatement.executeUpdate();
				preparedStatement.close();
			}
			catch (SQLException se)
			{
				// log the exception
				try 
		        {
					conn.close();
		        } 
		        catch (SQLException e) 
		        {
					e.printStackTrace();
		        }
			}
	  }
	  
	  /**
	   * This method deletes a specified trail. *NOTE* This method is never used as we ran out
	   * of time.
	   * @param name The name of the trail.
	   */
	  public void deleteTrail(String name)
      {
              
              if(name.contains("'")){
                      name = name.replace("'", "\\'");
              }
              try
              {                 
                      conn = DriverManager.getConnection(url);
                      preparedStatement = conn.prepareStatement("DELETE FROM trail_inventory WHERE " +
                      		"trail_name = " + "'" + name + "'");
                      preparedStatement.executeUpdate();
                      preparedStatement = conn.prepareStatement("DELETE FROM maint_user WHERE " +
                      		"trail_name = " + "'" + name + "'");
                      preparedStatement.executeUpdate();
                   
                      conn.close();
              }
              catch (SQLException ex)
              {
			          Logger lgr = Logger.getLogger(DBInterface.class.getName());
			          lgr.log(Level.SEVERE, ex.getMessage(), ex);
              } 
      	}
	  
	  /**
	   * This method is used for creating a custom query.
	   * @param query An SQL query
	   * @return The returning tuples if there are any.
	   */
	  public ResultSet customQuery(String query)
	  {
		  try
		  {
			  conn = DriverManager.getConnection(url);
			  preparedStatement = conn.prepareStatement(query);
			  rs = preparedStatement.executeQuery();
		  }
		  catch(SQLException exception)
		  {
			  Logger lgr = Logger.getLogger(DBInterface.class.getName());
	          lgr.log(Level.SEVERE, exception.getMessage(), exception);
		  }
		  return rs;
	  }
	  
	  /**
	   * This method closes a connection
	   */
	  public void closeConnection()
	  {
		  try 
		  {
			conn.close();
		  } 
		  catch (SQLException e) 
		  {
			e.printStackTrace();
		  }
	  }
}